﻿using Dapper;

using HBase;
using System;
using System.Collections.Generic;
using System.Linq;
using static Dapper.SqlMapper;

namespace Persistence {
    public class AbsDoiServer : AbsDoServer, IDoiServer
    { 

        public List<WhDoiDet> getDetials(long receiptOutId)
        {
            String sql = "select * from Wh_Doi_Det  where pid=@receiptOutId";
            return readConn().Query<WhDoiDet>(sql, new { receiptOutId }).ToList();
        }

        public Page<WhDoi> QueryReceiptInPage(DoDto dto) {
            string sql = " select i.* from Wh_doi i  where 1=1 ";

            if (!string.IsNullOrEmpty(dto.doNo)) {
                dto.doNo = "%" + dto.doNo + "%";
                sql += " and i.do_No like @doNo ";
            }
            if (dto.doType > 0) {
                sql += "and  i.do_type=@doType  ";
            }
            if (dto.status != 0) {
                sql += " and i.status = @status ";
            }

            if (dto.createBeginTime != DateTime.MinValue) {
                sql += " AND i.create_Time >= @createBeginTime ";
            }
            if (dto.createEndTime != DateTime.MinValue) {
                sql += " AND i.create_Time <= @createEndTime ";
            }

            if (!String.IsNullOrEmpty(dto.itemId)) {
                sql += " and i.id in (select pid from Wh_Doi_Det d where d.item_id=@itemId)";
            }

            dto.createEndTime = dto.createEndTime.AddDays(1);
            string orderBy = " i.create_Time  desc";

            return this.queryPage<WhDoi>(sql, orderBy, dto);
        }
        public Page<WhDoiDet> QueryReceiptOutDetailPage(DoDto dto)
        {
            string sql = @" SELECT  B.* FROM    dbo.Wh_Doi_Det B   where 1=1  ";

            if (!String.IsNullOrEmpty(dto.itemId))
            {
                sql += "and item_Id=@itemId ";
            }
            if (!String.IsNullOrEmpty(dto.batchNo))
            {
                dto.batchNo = "%" + dto.batchNo + "%";
                sql += "and batch_No like @batchNo ";
            }
            string orderBy = "id  desc";
            return this.queryPage<WhDoiDet>(sql, orderBy, dto);
        }
        




        public void DeleteDetailByReceptId(long receiptId)
        {
            string sql = @"DELETE FROM Wh_Doi_Det where pid=@receiptId";
            opConn().Execute(sql, new { receiptId = receiptId });
        }


        public List<WhDoExcel> GetExportList(DoDto dto)
        {
            string sql = @"SELECT i.do_No,d.finsh_Count,it.`code`,it.gg,it.name,it.unit , i.create_Time  FROM `wh_doi` i 
join wh_doi_det d on i.id =d.pid 
join core_item it on it.id=d.item_Id where 1=1";

            if (dto.createBeginTime != DateTime.MinValue) {
                sql += " AND i.create_Time >= @createBeginTime ";
            }
            if (dto.createEndTime != DateTime.MinValue) {
                sql += " AND i.create_Time <= @createEndTime ";
            }
            List<WhDoExcel> WhDoExcelDtos = readConn().Query<WhDoExcel>(sql,
                new { createBeginTime=dto.createBeginTime,
                    createEndTime=dto.createEndTime.AddDays(1) }).ToList();

            return WhDoExcelDtos;

        }
      

        public List<WhDoi> GetWhDoiByCode(string code)
        {
            String sql = "select * from Wh_Doi  where do_No=@code";
            return readConn().Query<WhDoi>(sql, new { code }).ToList();
        }

        public List<WhDoiDet> GetWhDoiDetByID(long id)
        {
            String sql = "select * from Wh_Doi_det  where xyd_id=@id";
            return readConn().Query<WhDoiDet>(sql, new { id }).ToList();
        }
    }
}
